<?php
/**
 * @version $Id: job.php 46 2011-09-27 10:42:49Z lnmtrung $
 */
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

class WrdControllerReports extends JControllerLegacy 
{
	protected  $option;
	protected  $controller;
	function __construct( $default = array()){
		$this->option = 'com_wrd';
		$this->controller = 'reports';
		parent::__construct( $default );
	}
	function display()
	{        
		//echo $this->getTask();die;
		switch($this->getTask()){
			case 'add':{
                JRequest::setVar( 'hidemainmenu', 1 );
                JRequest::setVar( 'layout', 'form'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exportpendinguser':{ 
                JRequest::setVar( 'layout', 'generalreport'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'membershipclubdetail':{ 
                JRequest::setVar( 'layout', 'membershipclub'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'memberstatisticfilter':{ 
                JRequest::setVar( 'layout', 'memberstatistic'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'complainfilter':{ 
                JRequest::setVar( 'layout', 'complainreport'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
			case 'memberbirthday':{ 
                JRequest::setVar( 'layout', 'memberbirthday'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
			case 'redemptiondetail':{ 
                JRequest::setVar( 'layout', 'redemptionreportnew'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'redemptionreportvoucher':{ 
                JRequest::setVar( 'layout', 'redemptionreportvoucher'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }			case 'redempreport':{ 				JRequest::setVar( 'layout', 'redempreport'  );				JRequest::setVar( 'view'  , 'reports');				break;            }
        /***** Excel.Start *****/ 
            case 'exredempreport':{ 
				// echo 123;exit;	
                JRequest::setVar( 'layout', 'redempreport'  );

                JRequest::setVar( 'view'  , 'reports');

                break;

            } 
			case 'excelpendinguser':{ 
                JRequest::setVar( 'layout', 'generalreport'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }     
            case 'exmemberspendingreportsum':{ 
                JRequest::setVar( 'layout', 'memberpendingreportsum'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exmemberspendingreportdetail':{ 
                JRequest::setVar( 'layout', 'memberpendingreportdetail'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exmembershipclub':{ 
                JRequest::setVar( 'layout', 'membershipclub'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exmembershipclubdetail':{ 
                JRequest::setVar( 'layout', 'membershipclub'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exmemberstatistic':{ 
                JRequest::setVar( 'layout', 'memberstatistic'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exmemberstatisticfilter':{ 
                JRequest::setVar( 'layout', 'memberstatistic'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }  
            case 'excomplainfilter':{ 
                JRequest::setVar( 'layout', 'complainreport'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exmemberservicereport':{ 
                JRequest::setVar( 'layout', 'memberservicereport'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
            case 'exredemptionreport':{ 
                JRequest::setVar( 'layout', 'redemptionreport'  );
                JRequest::setVar( 'view'  , 'reports');
                break;
            }
			case 'exredempvoucher':{ 
				JRequest::setVar( 'layout', 'redemptionreportvoucher'  );
				JRequest::setVar( 'view'  , 'reports');
				break;
            }			
			case 'exredempreportproduct':{ 
				JRequest::setVar( 'layout', 'redemptionreportproduct'  );
				JRequest::setVar( 'view'  , 'reports');
				break;
			}
			case 'exmemberbirthday':{ 
				JRequest::setVar( 'layout', 'memberbirthday'  );
				JRequest::setVar( 'view'  , 'reports');
				break;
			}
        /***** Excel.End *****/      
			
            default:
				JRequest::setVar( 'view'  , 'reports');
				break;
			}
		parent::display();
	}
	function saveVoucher(){
		$db = JFactory::getDbo();
		$date = JRequest::getVar("date");
		$outlet_name = JRequest::getVar("outlet_name");
		$outlet_voucher = JRequest::getInt("outlet_voucher");
		$id = JRequest::getInt("id");
		$sql = "UPDATE #__wrd_redem_report_voucher SET outlet_name_use_voucher = '".$outlet_name."',outlet_voucher='".$outlet_voucher."', date_voucher='".$date."',status=1 WHERE id='".$id."'";
		$db->setQuery($sql);$result = $db->query();
		echo $result;
		jexit();
	}
	function savePickup(){
		$db = JFactory::getDbo();
		$date = JRequest::getVar("date");
		//$outletName = JRequest::getVar("outlet_name");
		$outletCode = JRequest::getInt("outletCode");
		$id = JRequest::getInt("id");
		$sql = "UPDATE #__wrd_profile SET pick_up_date = '".$date."',status_of_card='3',mem_card_outlet='".$outletCode."', pick_up_at='".$outletCode."' WHERE id='".$id."'";
		$db->setQuery($sql);$result = $db->query();
		echo $result;
		jexit();
	}
	function view(){
		JRequest::setVar( 'layout', 'membership'  );
		JRequest::setVar( 'view'  , 'reports');
		parent::display();	
	}
	
	function greports(){
		JRequest::setVar( 'layout', 'redemption'  );
		JRequest::setVar( 'view'  , 'reports');
		parent::display();	
	}
	//Get code Redemption from vip_wrd_redem_report_voucher,dung de ajax goi trong report voucher
	//@author: hung.phan
	function getCodeRedem(){
		$sql = "SELECT DISTINCT code_redemption FROM #__wrd_redem_report_voucher WHERE 1";
		$db = JFactory::getDbo();$db->setQuery($sql);$rows = $db->loadObjectList();
		if(is_array($rows))
		{
			foreach($rows as $row){
				echo "{$row->code_redemption}\n";
			}
		}
		exit();
	}
	function getMemberid(){
	
		$sql = "SELECT DISTINCT member_id FROM #__wrd_redem_report_voucher WHERE 1";
		$db = JFactory::getDbo();$db->setQuery($sql);$rows = $db->loadObjectList();
		if(is_array($rows))
		{
			foreach($rows as $row){
				echo "{$row->member_id}\n";

			}
		}
		exit();
	}	function getVouchercode(){
	
		$sql = "SELECT DISTINCT code_voucher FROM #__wrd_redem_report_voucher WHERE 1";
		$db = JFactory::getDbo();$db->setQuery($sql);$rows = $db->loadObjectList();
		if(is_array($rows))
		{
			foreach($rows as $row){
				echo "{$row->code_voucher}\n";

			}
		}
		exit();
	}
	
	function send_redemp_notice_email()
	{
		// Check for request forgeries
		JRequest::checkToken() or jexit( 'Invalid Token' );

		$cid = JRequest::getVar( 'cid', array(), 'post', 'array' );
		JArrayHelper::toInteger($cid);
		if (count( $cid ) < 1) {
			JError::raiseError(500, JText::_( '<p style="color:red">Please select an item to send email</p>' ) );
		}
		
		$model = $this->getModel('reports');
		$model->send_email($cid, 'redemp_notice_email') ;
		$this->setRedirect('index.php?option='.$this->option.'&controller='.$this->controller.'&layout=redemption','Sent');
	}
	function send_redemp_pick_up_notice()
	{
		// Check for request forgeries
		JRequest::checkToken() or jexit( 'Invalid Token' );

		$cid = JRequest::getVar( 'cid', array(), 'post', 'array' );
		JArrayHelper::toInteger($cid);
		if (count( $cid ) < 1) {
			JError::raiseError(500, JText::_( '<p style="color:red">Please select an item to send email</p>' ) );
		}
		
		$model = $this->getModel('reports');
		$model->send_email($cid, 'redemp_pick_up_notice') ;
		$this->setRedirect('index.php?option='.$this->option.'&controller='.$this->controller.'&layout=redemption','Sent');
	}
    
    function transaction(){
        JRequest::setVar( 'layout', 'transaction'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
    }
    
    /*
     * Member Pending Report Detail
     */
    function memberpendingreportdetail(){
        JRequest::setVar( 'layout', 'memberpendingreportdetail'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
    } 
    
    /*
     * Member Statistic - PTD
     */
    function memberstatistic(){
        JRequest::setVar( 'layout', 'memberstatistic'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
    } 
    /*
     * Member Birthday
     */
    function memberbirthday(){
        JRequest::setVar( 'layout', 'memberbirthday'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
    }
    /*
     * Redemption report
     */
    function redemptionreport(){
        JRequest::setVar( 'layout', 'redemptionreport'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    }
    /*
     * Redemption report product
     */
    function redemptionreportproduct(){
        JRequest::setVar( 'layout', 'redemptionreportproduct'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
    }
	 /*
     * Redemption report
     */
    function redemptionreportvoucher(){
       
		JRequest::setVar( 'layout', 'redemptionreportvoucher'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    }
	/*
     * Approve Voucher
     */
    function approvevoucher(){
        JRequest::setVar( 'layout', 'approvevoucher'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    }
    /*
     * Complain report
     */
    function complainreport(){
        JRequest::setVar( 'layout', 'complainreport'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
    } 
    /*
     * Member Service report
     */
    function memberservicereport(){
        JRequest::setVar( 'layout', 'memberservicereport'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    } 
    
    /*
     * Member Ship Club
     */
    function membershipclub(){
        JRequest::setVar( 'layout', 'membershipclub'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    } 
    
    /*
     * Member Pending Report Summary
     */
    function memberpendingreportsum(){
        JRequest::setVar( 'layout', 'memberpendingreportsum'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    } 
    
    function generalreport(){
        JRequest::setVar( 'layout', 'generalreport'  );
        JRequest::setVar( 'view'  , 'reports');
        
        parent::display();    
    }    
    
    function exportexcel()
    {
        //die('excel');
        // Check for request forgeries
       
		JRequest::checkToken() or jexit( 'Invalid Token' );
        JRequest::setVar( 'layout', 'transaction'  );
        JRequest::setVar( 'view'  , 'reports');
        parent::display();    
        header('Location: http://'.$_SERVER['SERVER_NAME'].'/exportToExcel.php');
    }
    
    private function downloadFileExcel($fileName){
        
        $fileName .= '.xlsx';
        $fullPath = JPATH_ROOT.DS.'transactions'.DS.$fileName;
        //echo $fullPath; die;
        if( file_exists($fullPath) )
        {
            // Parse Info / Get Extension
            $fsize = filesize($fullPath);
            
            header("Pragma: public"); // required
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Cache-Control: private",false); // required for certain browsers
            header('Content-Type: application/vnd.ms-excel;');           
            header("Content-type: application/x-msexcel"); 
            header("Content-Disposition: attachment; filename=\"wmc.xlsx\";" );
            header("Content-Transfer-Encoding: binary");
            header("Content-Length: ".$fsize);
            ob_clean();
            flush();
            readfile( $fullPath );         
            //$this->setRedirect('index.php?option=com_wrd&controller='.$this->controller.'&layout=transaction');
            die;
        }
    }
    
	//List function danh cho Report System check 
	function checkProfileUser($num=true){
		$num = JRequest::getInt("num");
		
		if($num == 1){
			$db = JFactory::getDbo();
			$q = "SELECT count(*) AS total FROM #__wrd_profile AS p
						 WHERE p.user_id NOT IN ( SELECT id FROM #__users ) ";
			$db->setQuery($q);
			echo $db->loadResult(); exit();
		}
		
		elseif($num == 2){
			$db = JFactory::getDbo();
			$q = "SELECT count(*) AS total FROM #__wrd_transaction AS t
					 WHERE t.vip_number NOT IN ( SELECT member_id FROM #__wrd_profile ) ";
			$db->setQuery($q);
			echo $db->loadResult();exit();
		}
		
		elseif($num==3){
			echo "Undefine";exit();
		}
		elseif($num == 4){
			$db = JFactory::getDbo();
			$q = "SELECT count(*) FROM `#__wrd_point` WHERE user_id NOT IN (
						SELECT user_id FROM #__wrd_profile)";
			
			$db->setQuery($q);
			echo $db->loadResult();exit();
		}
		elseif($num == 5){
			$db = JFactory::getDbo();
			$q = "SELECT * FROM #__wrd_profile GROUP BY member_id HAVING count(member_id)>1";
			$db->setQuery($q);
			echo count($db->loadObjectList());exit();
		}
		elseif($num == 6){
			$db = JFactory::getDbo();
			$q = "SELECT count(*) FROM `#__wrd_point` AS p
					 WHERE (p.type=0) AND (p.transaction IS NOT NULL) AND (p.transaction
						NOT IN (SELECT transaction_id FROM #__wrd_transaction))";
			$db->setQuery($q);
			echo $db->loadResult();exit();
		}
		elseif($num == 7){
			$db = JFactory::getDbo();
			$q = "SELECT count(*) FROM `#__wrd_point` AS p
					 WHERE (p.user_id=0) OR (p.user_id IS NULL)";
			$db->setQuery($q);
			echo $db->loadResult();exit();
		}
		elseif($num == 8){
			$db = JFactory::getDbo();
			$q = "SELECT * FROM #__wrd_transaction GROUP BY transaction_id HAVING count( CONCAT( transaction_id, '', outlet ) ) >1";
			$db->setQuery($q);
			echo count($db->loadObjectList());exit();
		}
		elseif($num == 9){
			$db = JFactory::getDbo();
			$q = "SELECT * FROM #__wrd_profile GROUP BY p_email HAVING count(p_email)>1";
			$db->setQuery($q);
			echo count($db->loadObjectList());exit();
		}		else{
			echo 'nothing';
		}
		exit();
	} 
}